import mysql.connector
from mysql.connector import Error
from common.Loger_log import write_loging, error_loging

class MySQLDatabase:
    def __init__(self,host="124.70.153.49", user="ceshinizhuandashi", password="ceshinizhuandashi", database="testcase"):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None

    def connect(self):
        '''连接到数据库。'''
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            if self.connection.is_connected():
                write_loging(f"用户:{self.user},已连接到数据库{self.database}")
        except Error as e:
            error_loging(f"用户:{self.user}:连接到数据库{self.database}失败，\n失败原因{e}")

    def disconnect(self):
        '''断开与数据库的连接。'''
        if self.connection.is_connected():
            self.connection.close()
            write_loging(f"{self.user}:已断开与数据库{self.database}的连接")

    def execute_query(self, query, params=None):
        '''执行增、删、改操作。'''
        cursor = self.connection.cursor()
        try:
            if '%s' in query:
                if params and query.count('%s') != len(params):
                    raise ValueError("参数数量与占位符数量不匹配")
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            self.connection.commit()
            formatted_query = self.log_query(query, params)
            write_loging(f"{self.user}:执行SQL语句:{formatted_query}成功")
        except (Error, ValueError) as e:
            formatted_query = self.log_query(query, params)
            error_loging(f"{self.user}:执行SQL语句:{formatted_query}失败，\n失败原因{e}")
        finally:
            cursor.close()

    def fetch_all(self, query, params=None):
        '''执行查询操作，返回所有结果。'''
        cursor = self.connection.cursor(dictionary=True)
        result = None
        try:
            if '%s' in query:
                if params and query.count('%s') != len(params):
                    raise ValueError("参数数量与占位符数量不匹配")
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            result = cursor.fetchall()
            formatted_query = self.log_query(query, params)
            write_loging(f"{self.user}:执行SQL语句:{formatted_query}成功，\n数据库查询结果:{result}")
        except (Error, ValueError) as e:
            formatted_query = self.log_query(query, params)
            error_loging(f"{self.user}:执行SQL语句:{formatted_query}失败，\n失败原因{e}")
        finally:
            cursor.close()
        return result

    def fetch_one(self, query, params=None):
        '''执行查询操作，返回单个结果。'''
        cursor = self.connection.cursor(dictionary=True)
        result = None
        try:
            if '%s' in query:
                if params and query.count('%s') != len(params):
                    raise ValueError("参数数量与占位符数量不匹配")
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            result = cursor.fetchone()
            formatted_query = self.log_query(query, params)
            write_loging(f"{self.user}:执行SQL语句:{formatted_query}成功，\n数据库查询结果:{result}")
        except (Error, ValueError) as e:
            formatted_query = self.log_query(query, params)
            error_loging(f"{self.user}:执行SQL语句:{formatted_query}失败，\n失败原因{e}")
        finally:
            cursor.close()
        return result

    def log_query(self, query, params):
        '''将占位符替换为实际参数值，并返回格式化后的 SQL 语句。'''
        if params:
            formatted_query = query
            for param in params:
                formatted_query = formatted_query.replace("%s", repr(param), 1)
        else:
            formatted_query = query
        return formatted_query

# 使用示例
if __name__ == "__main__":
    db = MySQLDatabase()
    db.connect()


    # 插入数据
    #第一种方式
    insert_query = "insert into Student values('13' , '孙七' , '2014-06-01' , '女'); "
    db.execute_query(insert_query)
    #第二种方式
    insert_query = "insert into Student values(%s  , %s  , %s  , %s); "
    db.execute_query(insert_query, ("14", "李五", "2015-07-01", "男"))

    #查询所有用户数据
    select_query = "SELECT * FROM `Student` "
    rows = db.fetch_all(select_query)
    for row in rows:
        pass
        # print(row)

    #查询单条数据
    select_one_query = "SELECT * FROM `Student` WHERE `Sname` LIKE %s"
    row = db.fetch_one(select_one_query, ('赵雷',))


    #更新数据
    update_query = f"UPDATE `Student`  SET `Sname`=%s WHERE `SId`=%s;"
    db.execute_query(update_query, ( '测试逆转大师', '01',))

    #删除数据
    delete_query = "DELETE FROM `Student` WHERE Sname=%s"
    db.execute_query(delete_query, ('李五',))
    delete_query = "DELETE FROM `Student` WHERE Sname=%s"
    db.execute_query(delete_query, ('孙七',))
    db.disconnect()
